package com.ktjy.ktmall.dao.impl;

import com.ktjy.ktmall.dao.AttrItemDao;
import com.ktjy.ktmall.dao.BaseDao;
import com.ktjy.ktmall.entity.AttrItem;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @auth wzy
 * @date 2024/10/31 10:46
 */
public class AttrItemDaoImpl extends BaseDao implements AttrItemDao {
    @Override
    public List<AttrItem> findAttrByGoodsId(int goodsId) {
        List<AttrItem> list = new ArrayList<>();
        String sql = "select * from t_attr where FIND_IN_SET(`id`, (select GROUP_CONCAT(DISTINCT `attr_id` SEPARATOR ',') from t_attr_values where FIND_IN_SET(id, (select GROUP_CONCAT(attr_value_id SEPARATOR ',') from `t_goods_attr_values` where goods_id = ?))))";
        // select DISTINCT `attr_id` from t_attr_values 到 t_attr_values 属性值表中查询所有的
        // attr_id 为属性 id，因为属性 id 有重复的，所以使用 DISTINCT 进行去重。去重后再将多个属性 id 借助 GROUP_CONCAT()函数将多个属性 id 使用逗号进行连接。连接后的格式为：'1,2,3'，因为连接后的结果是字符串，所以最后还要借助 FIND_IN_SET()函数匹配 id 的值是否在连接后的字符串中存在。
        rs = super.executeQuery(sql, goodsId);
        try {
            while (rs.next()) {
                AttrItem a = new AttrItem(rs.getInt("id"), rs.getString("name"),
                        rs.getInt("order_num"), rs.getInt("enabled"),
                        rs.getString("create_time"), rs.getString("update_time"));
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public List<AttrItem> findAttrByStockSku(String stockSku) {
        List<AttrItem> list = new ArrayList<>();
        String sql = "select * from t_attr where FIND_IN_SET(`id`, (select GROUP_CONCAT(DISTINCT `attr_id` SEPARATOR ',') from t_attr_values where FIND_IN_SET(id, (select GROUP_CONCAT(attr_value_id SEPARATOR ',') from `t_sku_attr_values` where stock_sku = ?))))";
        rs = super.executeQuery(sql, stockSku);
        try {
            while (rs.next()) {
                AttrItem a = new AttrItem(rs.getInt("id"), rs.getString("name"),
                        rs.getInt("order_num"), rs.getInt("enabled"),
                        rs.getString("create_time"), rs.getString("update_time"));
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return list;
    }
}
